<?php

require_once dirname(__FILE__) . DIRECTORY_SEPARATOR . 'Lz.php';


abstract class LzSql
{
    static public
    function raw($sql, $vars = array())
    {
        return new LzSqlX(LzSqlX::RAW, $sql, $vars);
    }


    static public
    function field($name)
    {
        return new LzSqlX(LzSqlX::FIELD, $name);
    }


    public
    function data()
    {
        return $this->data;
    }


    public
    function clear()
    {
        $this->data = array();
        return $this;
    }


    public
    function query($query)
    {
        static $queries = array('select', 'insert', 'update', 'delete');

        $qry    = LzArr::pure($query);
        if (!isset($qry[0]) || !in_array((string)$qry[0], $queries))
            throw new LzBadUsage($query);

        return $this->{$qry[0]}($qry);
    }


    public
    function select($query)
    {
        static $clauses = array('fields', 'from', 'join', 'where',
                                'order', 'group', 'having', 'limit');

        $qry    = LzArr::pure($query);
        foreach ($clauses as $key)
        {
            $qry[$key]  = isset($qry[$key]) && !empty($key)
                    ? LzArr::pure($qry[$key])
                    : array();
        }

        $sql    = array();
        $sql[]  = 'SELECT ' . $this->parseFields($qry['fields']);
        $sql[]  = 'FROM ' . $this->from($qry['from']);
        if (!empty($qry['join']))
            $sql[]  = $this->join($qry['join']);
        foreach (array_slice($clauses, 3) as $clause)
        {
            if (!empty($qry[$clause]))
                $sql[]  = strtoupper($clause) . ' '
                        . $this->$clause($qry[$clause]);
        }
        return join(' ', $sql);
    }


    public
    function insert($query)
    {
        $fields = array();
        $values = array();
        Lz::def($query['values'], array());
        foreach (Lz::pure($query['values']) as $field => $value)
        {
            $fields[]   = $this->parseName($field);
            $values[]   = $this->parseVal($value);
        }
        return 'INSERT INTO ' . $this->parseName(Lz::def($query['into']))
                . '(' . join(', ', $fields) . ') VALUES ('
                . join(', ', $values) . ')';
    }


    public
    function update($query)
    {
        $values = array();
        Lz::def($query['values'], array());
        foreach (Lz::pure($query['values']) as $field => $value)
        {
            $values[]   = $this->parseName($field) . ' = '
                    . $this->parseVal($value);
        }
        return 'UPDATE ' . $this->parseName(Lz::def($query['table']))
                . ' SET ' . join(', ', $values)
                . ' WHERE ' . $this->where(Lz::def($query['where']));
    }


    public
    function delete($query)
    {
        return 'DELETE FROM ' . $this->parseName(Lz::def($query['from']))
                . ' WHERE ' . $this->where(Lz::def($query['where']));
    }


    public
    function from($from)
    {
        return $this->parseFields($from);
    }


    public
    function join($joins)
    {
        $res    = array();
        foreach ($joins as $join)
        {
            $join   = Lz::pure($join);
            $res[]  = strtoupper(Lz::def($join['type'])) . ' JOIN'
                    . ' ' . $this->parseFields(LzArr::first($join['table']))
                    . ' ON (' . $this->where(Lz::def($join['on'])) . ')';
        }
        return join(' ', $res);
    }


    public
    function where($conditions)
    {
        return $this->parseCmpList($conditions);
    }


    public
    function order($order)
    {
        $res    = array();
        foreach ($order as $field)
        {
            if ($field instanceof LzSqlX)
            {
                $res[]  = $this->parseValX($field);
            }
            else if (Lz::stringable($field))
            {
                list($name, $dir)   = preg_split('/\s+/', trim($field), 2)
                        + array('', 'ASC');

                if (!in_array($dir, array('ASC', 'DESC')))
                    throw new LzBadUsage($field);

                $res[]  = $this->parseName($field) . ' ' . $dir;
            }
            else
            {
                throw new LzBadUsage($field);
            }
        }
        return join(', ', $res);
    }


    public
    function group($group)
    {
        $res    = array();
        foreach ($order as $field)
            $res[]  = $this->parseName($field);
        return join(', ', $res);
    }


    public
    function having($conditions)
    {
        return $this->where($conditions);
    }


    public
    function limit($limit)
    {
        $lmt    = LzArr::pure($limit);
        if (1 == count($lmt))
            return $this->parseVal(LzArr::first($lmt));
        if (2 == count($lmt))
            return $this->parseVal(reset($lmt)) . $this->parseVal(next($lmt));
        throw new LzBadUsage($limit);
    }


    public
    function quoteName($field)
    {
        if (!Lz::stringable($field) || '' == (string)$field)
            throw new LzBadUsage($field);

        return $this->quoteDbName((string)$field);
    }


    abstract protected
    function quoteDbName($field);


    protected
    function parseFields($fields)
    {
        $res    = array();
        foreach (LzArr::pure($fields) as $key => $val)
        {
            $res[]  = is_string($key)
                    ? $this->parseVal($val) . ' ' . $this->quoteName($key)
                    : $this->quoteName($val);
        }
        return join(', ', $res);
    }


    protected
    function parseName($name)
    {
        if ($name instanceof LzSqlX)
            return $this->parseValX($name);

        if (!Lz::stringable($name))
            throw new LzBadUsage($name);

        return $this->quoteName($name);
    }


    /**
     *  Parses array of compare expressions and returns SQL string.
     *
     *  Values used in expressions are stored internally and my be
     *  retrieved using LzSql::data() method.
     *
     *  @param  $list   [array] Array of expressoins.
     *  @param  $type   [string] String to use to join expressionse.g.
     *          'AND, 'OR'. Spaces around the join string  are added
     *          automatically.
     *  @return         [string] SQL.
     */
    protected
    function parseCmpList($list, $type = 'AND')
    {
        if (Lz::void($list))
            return ('AND' == $type ? '1=1' : '0=1');

        $res    = array();
        foreach (LzArr::pure($list) as $key => $val)
        {
            $res[]  = is_string($key)
                    ? $this->parseCmp($key, $val)
                    : $this->parseCmp($val);
        }
        return join(" $type ", $res);
    }


    /**
     *  Returns compare expression SQL string.
     *
     *  @param  $key    [mixed] If $key is string it sould be in format
     *          '<field>[ <oper>]' where <field> is field name and <oper>
     *          is comparition operator. If <oper> is omited '=' is used.
     *          <field> is passed through $this->quoteName() and used on
     *          the left side of <oper>.
     *
     *          Allowed operators are:
     *              '=', '>', '<', '>=', '<=', '!=', 'LIKE', '!LIKE',
     *              'IN', '!IN', 'BETWEEN', '!BETWEEN'.
     *
     *          If $key is instance of LzSqlX its SQL is returned and $val
     *          is ignored.
     *
     *  @param  $val    [mixed] Value to be passed through $this->parseVal()
     *          and used on the right side of <oper>.
     *
     *  @return         [string]    SQL.
     */
    protected
    function parseCmp($key, $val = NULL)
    {
        if ($key instanceof LzSqlX)
            return $this->parseCmpX($key);

        if (Lz::eachable($key))
            return '(' . $this->parseCmpList($key) . ')';

        if (!Lz::stringable($key) || '' == (string)$key)
            throw new LzBadUsage($key);

        $key    = (string)$key;

        if (in_array((string)$key, array('OR', 'AND')))
            return '(' . $this->parseCmpList($val, $key) . ')';

        list($name, $op) = preg_split('/\s+/', trim($key), 2) + array('', '');
        if (in_array($op, array('IN', '!IN')))
        {
            $op = str_replace('!', 'NOT ', $op);
            return $this->quoteName($name) . " $op ("
                    . $this->parseArgList($val) . ')';
        }
        else if (in_array($op, array('BETWEEN', '!BETWEEN')))
        {
            if (!is_array($val) || 2 != count($val))
                throw new LzBadUsage($val);
            $op = str_replace('!', 'NOT ', $op);
            return $this->quoteName($name) . " $op "
                    . $this->parseVal(array_shift($val)) . ' AND '
                    . $this->parseVal(array_shift($val));
        }
        else if (in_array($op, $this->ops) || '' == $op)
        {
            Lz::def($op, '=');
            if ($op != '!=')
                $op = str_replace('!', 'NOT ', $op);
            return $this->quoteName($name) . " $op " . $this->parseVal($val);
        }
        else
        {
            throw new LzBadUsage($key);
        }
    }


    /**
     *  Returns value bind name or SQL string.
     *
     *  If bind name is returned value s stored internally and can be
     *  retrieved using LzSql::data() method.
     *
     *  @param  $val    [mixed] If value is instance of LzSqlX it will
     *          be parsed and its SQL returned. Otherwise its value is
     *          stored and its bind name returned.
     *  @return         [string] Bind name or SQL.
     */
    protected
    function parseVal($val)
    {
        if ($val instanceof LzSqlX)
            return $this->parseValX($val);

        $bind   = 'BIND' . count($this->data);
        $this->data[$bind]  = $val;
        return ":$bind";
    }


    /**
     *  Returns values as argument list in SQL.
     *
     *  @param  $list   [array] List of values. Keys are ignored.
     *  @return         [string] SQL.
     */
    protected
    function parseArgList($list)
    {
        $res    = array();
        foreach (LzArr::pure($list) as $val)
            $res[]  = $this->parseVal($val);
        return join(', ', $res);
    }


    protected
    function parseValX($ex)
    {
        $data   = $ex->data();
        switch ($ex->type())
        {
            case LzSqlX::RAW:
                if (!is_array($data) || 2 != count($data))
                    throw LzBadUsage($data);
                list($sql, $vars)   = $data;
                $this->data = array_merge($this->data, LzArr::pure($vars));
            return $sql;

            case LzSqlX::FIELD:
            return $this->quoteName($data);

            //case LzSqlX::FUNC:
            //    if (!is_array($data) || empty($data))
            //        throw new LzBadUsage($data);
            //    $name   = array_shift($data);
            //return $name . '(' . $this->parseArgList($data) . ')';

            default:
            throw new LzBadUsage($ex->type());
        }
    }


    protected
    function parseCmpX($ex)
    {
        $data   = $ex->data();
        switch ($ex->type())
        {
            case LzSqlX::RAW:
            return $this->parseValX($ex);

            //case LzSqlX::BETWEEN:
            //    if (!is_array($data) || 3 != count($data))
            //        throw new LzBadUsage($data);
            //    list($name, $min, $max) = $data;
            //return '(' . $this->parseVal($name) . ' >= '
            //        . $this->parseVal($min) . ' AND '
            //        . $this->quoteName($name) . ' <= '
            //        . $this->parseVal($min) . ')';
            //
            //case LzSqlX::CMP:
            //    if (!is_array($data) || 3 != count($data))
            //        throw new LzBadUsage($data);
            //    list($left, $op, $right) = $data;
            //    if (!in_array((string)$op, $this->ops))
            //        throw new LzBadUsage($op);
            //return $this->parseVal($left) . " $op " . $this->parseVal($right);

            default:
            throw new LzBadUsage($ex->type());
        }
    }


    protected   $data   = array();
    protected   $ops    = array('=', '>', '<', '>=', '<=', '!=',
                            'LIKE', '!LIKE');
}


class LzSqlX
{
    const   RAW     = 'raw';
    const   FIELD   = 'field';
    //const   FUNC    = 'func';
    //const   BETWEEN = 'between';
    //const   CMP     = 'cmp';


    public
    function __construct($type, $data = NULL)
    {
        $this->type = $type;
        $this->data = (func_num_args() > 2)
                ? array_slice(func_get_args(), 1)
                : $data;
    }


    public
    function type()
    {
        return $this->type;
    }


    public
    function data()
    {
        return $this->data;
    }


    protected   $type;
    protected   $data;
}

